package ch.sv7.tool.dbmanager.db;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Arrays;
import java.util.List;

import junit.framework.TestCase;

import org.w3c.dom.Document;

import ch.sv7.tool.dbmanager.db.dialect.DefaultDialect;
import ch.sv7.tool.dbmanager.db.script.ScriptTableWriter;
import ch.sv7.tool.dbmanager.db.sql.SQLDatabaseSchemaFactory;
import ch.sv7.tool.dbmanager.db.sql.SQLFullTableReader;
import ch.sv7.tool.dbmanager.db.sql.SQLTableWriter;
import ch.sv7.tool.dbmanager.db.xml.SaxXmlTableReader;
import ch.sv7.tool.dbmanager.db.xml.StringXmlTableWriter;
import ch.sv7.tool.dbmanager.db.xml.XmlDatabaseSchemaExport;
import ch.sv7.tool.dbmanager.types.ConnectionTypesInfo;
import ch.sv7.tool.dbmanager.util.FileSystemUtils;
import ch.sv7.tool.dbmanager.util.SQLHelper;

/**
 *
 * @author svt
 *
 */
public class BackupRestoreTestCase extends TestCase {

	Connection conn = null;

	protected void setUp() throws Exception {
		super.setUp();
		
		Class.forName("org.hsqldb.jdbcDriver");
		conn = DriverManager.getConnection("jdbc:hsqldb:"
                + "mem:mydb",    // Memory
                "sa",                     // username
                "");                      // password
	}

	public void testBackupRestore() throws Exception {
		SQLDatabaseSchemaFactory factory = new SQLDatabaseSchemaFactory(conn, new DefaultDialect(new ConnectionTypesInfo(conn)));
		List tables = Arrays.asList(factory.getTables());
		assertEquals(0, tables.size());
		
		File currentFolder = FileSystemUtils.currentFolder(this);
		File tablesFolder = new File(currentFolder, "tables");
		tablesFolder.mkdirs();
		String folder = tablesFolder.getAbsolutePath();
		
		SQLHelper.executeSQL("CREATE TABLE MYTABLE(name VARCHAR, number INTEGER)", conn);
		SQLHelper.executeSQL("INSERT INTO MYTABLE(name, number) VALUES ('TEST', 2)", conn);
		SQLHelper.executeSQL("INSERT INTO MYTABLE(name, number) VALUES ('TEST1', 3)", conn);
		factory = new SQLDatabaseSchemaFactory(conn, new DefaultDialect(new ConnectionTypesInfo(conn)));
		tables = Arrays.asList(factory.getTables());
		assertEquals(1, tables.size());
		tables.contains("mytable");
		DatabaseSchema dbSchema = factory.getDatabaseSchema();

		
		TableReader reader = new SQLFullTableReader(conn);
		TableWriter writer = new StringXmlTableWriter(folder, null,
				"TABLENAME", /* isFileZipped*/ false, "UTF-8");

		Manager.copy(reader, writer, dbSchema, "MYTABLE");		
		
		//SQLHelper.executeSQL("DELETE from mytable", conn);
		
		reader = new SaxXmlTableReader(folder, null, "TABLENAME", false);
		writer = new SQLTableWriter(conn, true /*deleteTable*/, false/*overwrite*/);
		
		Manager.copy(reader, writer, dbSchema, "MYTABLE");
		
		File schemaFolder = new File(currentFolder, "schema");
		schemaFolder.mkdirs();
		String schemaFolderPath = schemaFolder.getAbsolutePath();
		XmlDatabaseSchemaExport exporter = new XmlDatabaseSchemaExport();
		Document xmlDoc = exporter.exportAsXml(dbSchema);
		assertNotNull(xmlDoc);
		exporter.exportToFile(new File(schemaFolder, "schema.xml").getAbsolutePath(), dbSchema);
		
		
		SQLHelper.executeSQL("DROP table mytable", conn);
	
	};
	
	public void testScriptCreation() throws Exception {
		SQLDatabaseSchemaFactory factory = new SQLDatabaseSchemaFactory(conn, new DefaultDialect(new ConnectionTypesInfo(conn)));
		List tables = Arrays.asList(factory.getTables());
		assertEquals(0, tables.size());
		
		File currentFolder = FileSystemUtils.currentFolder(this);
		File tablesFolder = new File(currentFolder, "tables-sql");
		tablesFolder.mkdirs();
		String folder = tablesFolder.getAbsolutePath();
		
		SQLHelper.executeSQL("CREATE TABLE MYTABLE(name VARCHAR, number INTEGER)", conn);
		SQLHelper.executeSQL("INSERT INTO MYTABLE(name, number) VALUES ('TEST', 2)", conn);
		SQLHelper.executeSQL("INSERT INTO MYTABLE(name, number) VALUES ('TEST1', 3)", conn);
		factory = new SQLDatabaseSchemaFactory(conn, new DefaultDialect(new ConnectionTypesInfo(conn)));
		tables = Arrays.asList(factory.getTables());
		assertEquals(1, tables.size());
		tables.contains("mytable");
		DatabaseSchema dbSchema = factory.getDatabaseSchema();

		
		TableReader reader = new SQLFullTableReader(conn);
		TableWriter writer = new ScriptTableWriter(folder, null, "TABLENAME");

		Manager.copy(reader, writer, dbSchema, "MYTABLE");		
		
		
		SQLHelper.executeSQL("DROP table mytable", conn);
	
	};
	
	protected void tearDown() throws Exception {
		if (conn != null) {
			conn.close();
		}
		super.tearDown();
	}

	
	
}
